<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Inv_release_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }


   public function get_request(){
      $sql = "SELECT * FROM inv_item_request WHERE inv_request_status = 'REQ_APPROVED' OR inv_request_status = 'REQ_PRE-RELEASED' ";
      $query = $this->db->query($sql);
      return $query->result();
   }

   public function get_request_id($id){
    $sql = "SELECT iir.* ,au1.first_name reqfn, au1.last_name reqln, au2.first_name apvfn ,au2.last_name apvln , alv.* FROM inv_item_request iir
    INNER JOIN app_users au1 on au1.user_id = iir.inv_requested_by 
    INNER JOIN app_users au2 on au2.user_id = iir.inv_approved_by 
    INNER JOIN app_lookup_values alv on iir.inv_request_status = alv.lookup_code WHERE iir.inv_request_id = ".$id. " ";
    $query = $this->db->query($sql);
    return $query->result();

   }

   public function get_contents($id){
      $sql = "SELECT iic.*, ii.description FROM inv_itemreq_content iic
                    INNER JOIN inv_items ii on ii.item_id = iic.item_id  
              WHERE iic.inv_request_id = ".$id." ";
      $query = $this->db->query($sql);
      echo json_encode($query->result());
   }

   // public function get_item_contents($id){
   //  $sql = "SELECT iir."
   // }

   public function insert_release($data){
    $this->db->query("call inv_release_ins(?,?,?,?,@id,@err,@msg)", $data);
        $que = $this->db->query("SELECT @id as id , @err as error , @msg as msg");
        echo json_encode($que->result());
   }

   public function get_current_status($id){
      $sql = "SELECT iic.inv_request_id,
                    (SELECT(CASE WHEN sum(iic.inv_released_qty) = sum(iic.inv_item_qty) THEN 'REQ_RELEASED' 
                                WHEN sum(iic.inv_released_qty) != 0 THEN 'REQ_PRE-RELEASED' 
                                ELSE 'REQ_APPROVED' END )) as status
          FROM inv_itemreq_content iic
          WHERE iic.inv_request_id = ".$id."
          ";
      $query = $this->db->query($sql);
    return $query->result();
   }

   public function insert_contents_req($data){
      $this->db->query("call inv_release_content_ins(?,?,?,?,?,?,?,?,?,@err,@msg)", $data);
        $que = $this->db->query("SELECT @err as error , @msg as msg");
      return $que->result();
   }

   public function get_picks($id){


    $sql = "SELECT it.inv_onhand_id onhand_id
                    ,iic.req_content_id
                    ,iic.inv_item_qty
                    ,iic.item_id
                    ,it.inv_sub_id
                    ,it.locator_id
                    ,ii.item_num item_num
                    ,ii.description i_desc
                    ,iis.inv_sub_code sub_code
                    ,iis.inv_sub_name sub_name
                    ,isl.locator_code loc_code
                    ,isl.locator_description loc_desc
                    ,it.item_qty item_qty
                    ,it.reorder_lvl reorder_lvl
                  FROM inv_on_hand it
                    INNER JOIN inv_items ii ON it.item_id = ii.item_id
                    INNER JOIN inv_sub iis ON iis.inv_sub_id = it.inv_sub_id
                    INNER JOIN inv_sub_locator isl ON isl.locator_id = it.locator_id
                    INNER JOIN inv_itemreq_content iic ON iic.item_id = it.item_id
                    WHERE iic.req_content_id = ".$id." ORDER BY it.item_qty DESC";

       $query = $this->db->query($sql);
      return $query->result();

    // $sql = "SELECT it.inv_onhand_id onhand_id
    //                 ,iic.req_content_id
    //                 ,iic.inv_item_qty
    //                 ,ii.item_num item_num
    //                 ,ii.description i_desc
    //                 ,iis.inv_sub_code sub_code
    //                 ,iis.inv_sub_name sub_name
    //                 ,isl.locator_code loc_code
    //                 ,isl.locator_description loc_desc
    //                 ,it.item_qty item_qty
    //                 ,it.reorder_lvl reorder_lvl
    //               FROM inv_on_hand it
    //                 INNER JOIN inv_items ii ON it.item_id = ii.item_id
    //                 INNER JOIN inv_sub iis ON iis.inv_sub_id = it.inv_sub_id
    //                 INNER JOIN inv_sub_locator isl ON isl.locator_id = it.locator_id
    //                 INNER JOIN inv_itemreq_content iic ON iic.item_id = it.item_id
    //                 WHERE iic.req_content_id = ".$id." ORDER BY it.item_qty DESC
    //                 ";
    // $query = $this->db->query($sql);  
    // echo json_encode($query->result($sql));

   }
}